﻿using Spire.Xls;
using Spire.Xls.Charts;
using System.Drawing;

namespace ClosedXML_ExcelDemo
{
    public static class Illustrations
    {
        public static void InsertPicture(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input);
            Worksheet ws = wb.Worksheets[0];
            ws.Pictures.Add(13, 1, "../../Documents/Chart.png");
            wb.SaveToFile(output);
        }

        public static void InsertChart(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input);
            Worksheet ws = wb.Worksheets[0];

            Chart chart = ws.Charts.Add(ExcelChartType.ColumnClustered);
            chart.DataRange = ws.Range["B2:E11"];
            chart.SeriesDataFromRange = false;

            chart.LeftColumn = 1;
            chart.TopRow = 13;
            chart.RightColumn = 16;
            chart.BottomRow = 33;

            chart.ChartTitle = "Sales market by country";
            chart.ChartTitleArea.IsBold = true;

            chart.PrimaryCategoryAxis.Title = "Country";
            chart.PrimaryCategoryAxis.TitleArea.IsBold = true;

            chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
            chart.PrimaryValueAxis.TitleArea.IsBold = true;

            foreach (ChartSerie cs in chart.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasCategoryName = false;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasSeriesName = false;
                cs.DataPoints.DefaultDataPoint.DataLabels.Position = DataLabelPositionType.Center;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasLegendKey = false;
            }

            chart.Legend.Position = LegendPositionType.Bottom;
            wb.SaveToFile(output);
        }

        public static void InsertSparklines(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input);
            Worksheet ws = wb.Worksheets[0];

            SparklineGroup sparklineGroup = ws.SparklineGroups.AddGroup(SparklineType.Line);
            SparklineCollection sparklines = sparklineGroup.Add();
            sparklines.Add(ws["D3:G3"], ws["H3"]);
            sparklines.Add(ws["D4:G4"], ws["H4"]);
            sparklines.Add(ws["D5:G5"], ws["H5"]);
            sparklines.Add(ws["D6:G6"], ws["H6"]);

            wb.SaveToFile(output);
        }

        public static void SaveChartToPicture(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input);
            Image[] imgs = wb.SaveChartAsImage(wb.Worksheets[0]);
            for (int i = 0; i < imgs.Length; i++)
            {
                imgs[i].Save(string.Format("{0}-{1}.png", output, i));
            }
        }

        public static void SetBackgroundImage(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input);
            Worksheet ws = wb.Worksheets[0];
            ws.PageSetup.BackgoundImage = new Bitmap("../../Documents/Background.jpg");
            wb.SaveToFile(output);
        }
    }
}